﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;
using System.Linq;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private int actionId = 0;
        private int typeActId = 0;
        private int typePrizeId = 0;
        private DataTable dt_drugs;
        private DateTime dateStart_prev;
        private DateTime dateEnd_prev;

        //private DataTable drugsForPlan;
        //private DataTable pharmForPlan;

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");

                dataGridView2.RowHeadersVisible = false;
                dataGridView2.Columns.Add("Код", "Код");
                dataGridView2.Columns.Add("Аптека", "Аптека");
                SellAllPharm();

                dataGridView3.RowHeadersVisible = false;
                dataGridView3.Columns.Add("ТТ", "ТТ");
                dataGridView3.Columns.Add("Код", "Код");
                dataGridView3.Columns["Код"].Visible = false;
                SellAllTT();

                dt_drugs = new DataTable();
                //dt_drugs.Columns.Add("code");
                //DrugCodeZero();

                DataTable modelPlan = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_ModelPlanSel", 30, new SqlStorProc.sp_params[0]);
                if (modelPlan.Rows.Count > 0)
                {
                    foreach (DataRow row in modelPlan.Rows)
                    {
                        comboBox1.Items.Add(new NewItem() { ParamID = row[0], ParamComment = row[1].ToString() });
                    }
                }
                comboBox1.SelectedIndex = 0;
                groupBox13.Enabled = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                this.Close();
            }
        }

        void DrugCodeZero()
        {
            dt_drugs.Rows.Clear();
            dt_drugs.Rows.Add(new object[1] { 0 });
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Form2 actForm = new Form2();
            actForm.conn = conn;
            try
            {
                if (actForm.ShowDialog() == DialogResult.OK)
                {
                    textBox1.Text = actForm.nameAct;
                    label4.Text = "Тип акции: " + actForm.typeAct;
                    label1.Text = "Состояние: " + actForm.destr;
                    label7.Text = "Премия: " + actForm.typePrize;
                    dateTimePicker1.Value = actForm.dateStart;
                    dateTimePicker2.Value = actForm.dateEnd;
                    dt_drugs.Clear();
                    groupBox7.Text = "Товары";
                    SellAllTT();
                    SellAllPharm();
                    if (actForm.typeAct == "Товары")
                    {
                        groupBox7.Enabled = true;
                        groupBox14.Enabled = false;
                        groupBox8.Enabled = false;
                    }
                    else
                        if (actForm.typeAct == "Торговые технологии")
                        {
                            groupBox7.Enabled = false;
                            groupBox14.Enabled = false;
                            groupBox8.Enabled = true;
                        }
                        else
                        {
                            if (actForm.typeAct == "Группа товаров")
                            {
                                groupBox7.Enabled = true;
                                groupBox14.Enabled = true;
                                groupBox8.Enabled = false;
                            }
                            else
                            {
                                groupBox7.Enabled = true;
                                groupBox8.Enabled = true;
                                groupBox14.Enabled = true;
                            }
                        }

                    actionId = actForm.actionId;
                    typeActId = actForm.typeActId;
                    typePrizeId = actForm.typePrizeId;
                    SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[2];
                    param[0].name = "@actionid";
                    param[0].value = actionId;
                    param[1].name = "@pharmcode";
                    DataTable pharmTable = new DataTable();
                    pharmTable.Columns.Add("item");
                    if(dataGridView2.RowCount > 1 || dataGridView2[0,0].Value.ToString() != "0")
                        foreach(DataGridViewRow row in dataGridView2.Rows)
                            pharmTable.Rows.Add(row.Cells[0].Value);
                    param[1].value = pharmTable;
                    dataGridView1.DataSource = null;
                    dataGridView1.DataSource = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_ActionsDetailsSel", 300, param).DefaultView;
                    groupBox13.Enabled = true;
                    foreach (DataGridViewColumn col in dataGridView1.Columns)
                    {
                        if (col.HeaderText.IndexOf("*") == col.HeaderText.Length - 1) col.ReadOnly = false;
                        else col.ReadOnly = true;
                    }
                    dateStart_prev = DateTime.Now.Date;
                    dateEnd_prev = DateTime.Now.Date;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                actForm.Close();
                actForm = null;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();
            try
            {
                DataTable dt_pharm = new DataTable();

                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[0];
                dt_pharm = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_PharmSel", 300, par);
                foreach (DataColumn dc in dt_pharm.Columns)
                {
                    if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                    else dc.ReadOnly = false;
                }
                if (dataGridView2.RowCount > 0 && dt_pharm.Rows.Count > 0)
                {
                    foreach (DataGridViewRow row in dataGridView2.Rows)
                    {
                        foreach (DataRow row1 in dt_pharm.Rows)
                        {
                            if (Convert.ToInt32(row1["Код"]) == Convert.ToInt32(row.Cells["Код"].Value))
                            {
                                row1[namechcol] = true;
                                break;
                            }
                        }
                    }
                }
                f3.namechcol = namechcol;
                f3.Text = "Фильтр по аптекам";
                f3.sortcoumn = "Аптека";
                f3.dataGridView1.DataSource = dt_pharm.DefaultView;
                f3.ShowDialog();
                dataGridView2.Rows.Clear();

                foreach (DataRow dr in dt_pharm.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]) == true)
                    {
                        dataGridView2.Rows.Add(new object[2] { dr["Код"], dr["Аптека"] });
                    }
                }
                if (dataGridView2.RowCount == 0)
                {
                    SellAllPharm();
                }
                else groupBox5.Text = "Выбранные аптеки (всего выбрано " + dataGridView2.RowCount + ")";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                f3.Close();
                f3 = null;
            }
        }

        void SellAllPharm()
        {
            dataGridView2.Rows.Clear();
            dataGridView2.Rows.Add(new object[2] { 0, "Все аптеки" });
            dataGridView2.Columns["Код"].Width /= 2;
            dataGridView2.Columns["Аптека"].Width = dataGridView2.Width - dataGridView2.Columns["Код"].Width - 3;           
            groupBox5.Text = "Выбранные аптеки";
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SellAllPharm();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();
            try
            {
                DataTable dt_pharm = new DataTable();

                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[0];
                dt_pharm = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_TradeTechSel", 300, par);
                foreach (DataColumn dc in dt_pharm.Columns)
                {
                    if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                    else dc.ReadOnly = false;
                }
                if (dataGridView3.RowCount > 0 && dt_pharm.Rows.Count > 0)
                {
                    foreach (DataGridViewRow row in dataGridView2.Rows)
                    {
                        foreach (DataRow row1 in dt_pharm.Rows)
                        {
                            if (Convert.ToInt32(row1["Код"]) == Convert.ToInt32(row.Cells["Код"].Value))
                            {
                                row1[namechcol] = true;
                                break;
                            }
                        }
                    }
                }
                f3.namechcol = namechcol;
                f3.Text = "Фильтр по торговым технологиям";
                f3.sortcoumn = "ТТ";
                f3.dataGridView1.DataSource = dt_pharm.DefaultView;
                f3.ShowDialog();
                dataGridView3.Rows.Clear();

                foreach (DataRow dr in dt_pharm.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]) == true)
                    {
                        dataGridView3.Rows.Add(new object[2] { dr["ТТ"], dr["Код"] });
                    }
                }
                if (dataGridView3.RowCount == 0)
                {
                    SellAllTT();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                f3.Close();
                f3 = null;
            }
        }
        void SellAllTT()
        {
            dataGridView3.Rows.Clear();
            dataGridView3.Rows.Add(new object[2] { "Все ТТ", 0 });
            dataGridView3.Columns["ТТ"].Width = dataGridView3.Width - 3;   
        }

        private void button4_Click(object sender, EventArgs e)
        {
            DrugsFilter.DrugsFilter f2 = new DrugsFilter.DrugsFilter();

            if (typeActId == 3)
            {
                SqlStorProc.sp_params[] par1 = new SqlStorProc.sp_params[1];
                par1[0].name = "@actionid";
                par1[0].value = actionId;
                dt_drugs = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_DrugGroupSel", 300, par1);
                dt_drugs.Columns.RemoveAt(1);
                if (dt_drugs.Rows.Count > 0)
                {
                    par1[0].name = "@drugslist";
                    par1[0].value = dt_drugs;
                    dt_drugs = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_AllDrugsSel", 300, par1);
                }
            }
            if (dt_drugs.Rows.Count == 0)
            {
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[2];
                par[0].name = "@arch";
                par[0].value = "нет";
                par[1].name = "@top";
                par[1].value = "3000";

                f2.dt_drug = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_AllDrugsSel", 300, par).Copy();
                foreach (DataColumn dc in f2.dt_drug.Columns)
                {
                    if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                    else dc.ReadOnly = false;
                }
                f2.rb = true;
                f2.dt_drug_tmp = f2.dt_drug.Clone();
            }
            else
            {
                f2.dt_drug = dt_drugs.Clone();
                f2.dt_drug_tmp = dt_drugs.Copy();
            }
            f2.sqlconn = conn;
            f2.namechcol = namechcol;

            if (dt_drugs.Rows.Count == 0)
                f2.dataGridView1.DataSource = f2.dt_drug.DefaultView;
            else
                f2.dataGridView1.DataSource = f2.dt_drug_tmp.DefaultView;
            //f2.dt_br = dt_brand;
            f2.ShowDialog();
            dt_drugs = f2.dt_drug_tmp.Copy();

            groupBox7.Text = "Товары (всего выбрано " + dt_drugs.Rows.Count + ")";
            
            f2.Close();
            f2 = null;
        }

        private void button6_Click(object sender, EventArgs e)
        {
            Form4 f4 = new Form4();
            f4.conn = conn;
            f4.ShowDialog();
            f4.Close();
            f4 = null;
        }

        private void button7_Click(object sender, EventArgs e)
        {
            Form5 f5 = new Form5();
            f5.conn = conn;
            if (actionId > 0)
            {
                f5.actionId = actionId;
                f5.date1 = dateTimePicker1.Value.Date;
                f5.date2 = dateTimePicker2.Value.Date;
            }
            f5.ShowDialog();
            f5.Close();
            f5 = null;
        }

        private void button8_Click(object sender, EventArgs e)
        {
            Form6 f6 = new Form6();
            f6.conn = conn;
            f6.actionId = actionId;
            f6.ShowDialog();
            f6.Close();
            f6 = null;
        }

        private void button9_Click(object sender, EventArgs e)
        {
            try
            {
                if (actionId == 0) MessageBox.Show("Выберите акцию");
                else
                {
                    if ((Convert.ToInt32((comboBox1.SelectedItem as NewItem).ParamID) == 6 && !(typeActId == 1 || typeActId == 3)) ||
                        (Convert.ToInt32((comboBox1.SelectedItem as NewItem).ParamID) == 5 && typeActId != 1))
                    {
                        MessageBox.Show("Для данного типа акции невозможно произвести расчет.\nЛибо поменяйте акцию, либо измените 'Вариант планирования'");
                    }
                    else
                    {
                        if (Convert.ToInt32((comboBox1.SelectedItem as NewItem).ParamID) == 6) MessageBox.Show("Внимание! Расчет будет производиться по не акционным товарам");
                        SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[7];
                        par[1].name = "@typeact";
                        if (typeActId == 1)
                        {
                            par[0].name = "@drugcode";
                            DataTable drugs = new DataTable();
                            drugs.Columns.Add("Item");
                            foreach (DataRow row in dt_drugs.Rows)
                                drugs.Rows.Add(row[1]);
                            par[0].value = drugs;

                            par[1].value = typeActId;
                        }
                        if (typeActId == 3)
                        {
                            par[0].name = "@drugcode";
                            par[1].value = typeActId;
                        }
                        if (typeActId == 2)
                        {
                            par[0].name = "@ttcode";
                            DataTable tt = new DataTable();
                            tt.Columns.Add("Item");
                            if (dataGridView3.RowCount > 1 || Convert.ToInt32(dataGridView3.Rows[0].Cells["Код"].Value) != 0)
                                foreach (DataGridViewRow row in dataGridView3.Rows)
                                    tt.Rows.Add(row.Cells["Код"].Value);
                            par[0].value = tt;

                            par[1].value = typeActId;
                        }

                        par[2].name = "@pharmcode";
                        DataTable ph = new DataTable();
                        ph.Columns.Add("Item");
                        if (dataGridView2.RowCount > 1 || Convert.ToInt32(dataGridView2.Rows[0].Cells["Код"].Value) != 0)
                            foreach (DataGridViewRow row in dataGridView2.Rows)
                                ph.Rows.Add(row.Cells["Код"].Value);
                        par[2].value = ph;

                        par[3].name = "@date1";
                        par[3].value = dateTimePicker3.Value.Date;

                        par[4].name = "@date2";
                        par[4].value = dateTimePicker4.Value.Date;

                        par[5].name = "@drugparent";
                        if (Convert.ToInt32((comboBox1.SelectedItem as NewItem).ParamID) == 6) par[5].value = 1;
                        else par[5].value = 0;

                        par[6].name = "@actionid";
                        par[6].value = actionId;

                        dataGridView1.DataSource = null;
                        dataGridView1.DataSource = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_SaleReport", 600, par).DefaultView;
                        groupBox13.Enabled = false;
                        foreach (DataGridViewColumn col in dataGridView1.Columns)
                        {
                            col.ReadOnly = true;
                        }
                    }
                    dateStart_prev = dateTimePicker3.Value;
                    dateEnd_prev = dateTimePicker4.Value;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                groupBox13.Enabled = false;
            }
            
        }

        private void button10_Click(object sender, EventArgs e)
        {
            try
            {
                if (comboBox1.SelectedIndex > -1)
                {
                    switch (Convert.ToInt32((comboBox1.SelectedItem as NewItem).ParamID))
                    {
                        case 1:
                            ModelPlan1();
                            groupBox13.Enabled = true;
                            foreach (DataGridViewColumn col in dataGridView1.Columns)
                            {
                                if (col.HeaderText.IndexOf("*") == col.HeaderText.Length - 1) col.ReadOnly = false;
                                else col.ReadOnly = true;
                            }
                            break;
                        case 2:
                            ModelPlan2();
                            groupBox13.Enabled = true;
                            foreach (DataGridViewColumn col in dataGridView1.Columns)
                            {
                                if (col.HeaderText.IndexOf("*") == col.HeaderText.Length - 1) col.ReadOnly = false;
                                else col.ReadOnly = true;
                            }
                            break;
                        case 3:
                            ModelPlan3();
                            groupBox13.Enabled = true;
                            foreach (DataGridViewColumn col in dataGridView1.Columns)
                            {
                                if (col.HeaderText.IndexOf("*") == col.HeaderText.Length - 1) col.ReadOnly = false;
                                else col.ReadOnly = true;
                            }
                            break;
                        case 4:
                            ModelPlan4();
                            groupBox13.Enabled = true;
                            foreach (DataGridViewColumn col in dataGridView1.Columns)
                            {
                                if (col.HeaderText.IndexOf("*") == col.HeaderText.Length - 1) col.ReadOnly = false;
                                else col.ReadOnly = true;
                            }
                            break;
                        case 5:
                            ModelPlan5();
                            groupBox13.Enabled = true;
                            foreach (DataGridViewColumn col in dataGridView1.Columns)
                            {
                                if (col.HeaderText.IndexOf("*") == col.HeaderText.Length - 1) col.ReadOnly = false;
                                else col.ReadOnly = true;
                            }
                            break;
                        case 6:
                            ModelPlan6();
                            groupBox13.Enabled = true;
                            foreach (DataGridViewColumn col in dataGridView1.Columns)
                            {
                                if (col.HeaderText.IndexOf("*") == col.HeaderText.Length - 1) col.ReadOnly = false;
                                else col.ReadOnly = true;
                            }
                            break;
                        default:
                            MessageBox.Show("Этот вариант еще не реализован");
                            groupBox13.Enabled = false;
                            break;
                    }
                }
                else MessageBox.Show("Не выбран вариант планирования");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void ModelPlan1()
        {
            try
            {
                if (dataGridView1.RowCount > 0)
                {
                    DataTable source = (dataGridView1.DataSource as DataView).ToTable();
                    string name = "";
                    string code = "";
                    if (source.Columns.Contains("Товар"))
                    {
                        name = "Товар";
                        code = "Код товара";
                    }
                    else if (source.Columns.Contains("ТТ"))
                    {
                        name = "ТТ";
                        code = "Код ТТ";
                    }
                    else if (source.Columns.Contains("Код группы"))
                    {
                        name = "Группа товаров";
                        code = "Код группы";
                    }
                    DataView view = new DataView(source);
                    view.RowFilter = "[" + name + "] <> ' Итого'";
                    DataTable drugsForPlan;

                    drugsForPlan = view.ToTable(true, code, name);
                    drugsForPlan.Columns.Add("Процент от продаж"); 
                    
                    foreach (DataRow row in drugsForPlan.Rows) 
                        if(row[2].ToString() == "") row[2] = 100;

                    Form7 f7 = new Form7();
                    f7.formText = "Процент от количества проданных упаковок";
                    f7.gbText = "Процент от продаж упаковок";
                    f7.dt = drugsForPlan;
                    f7.tbText = "100";
                    if (f7.ShowDialog() == DialogResult.OK)
                    {
                        drugsForPlan = f7.dt;
                        drugsForPlan.Columns.RemoveAt(1);

                        source.Columns.RemoveAt(3);
                        source.Columns.RemoveAt(1);
                        while (source.Columns.Count > 5) source.Columns.RemoveAt(5);
                        SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[5];
                        param[0].name = "@perc";
                        param[0].value = drugsForPlan;
                        param[1].name = "@sales";
                        param[1].value = source;
                        param[2].name = "@actionid";
                        param[2].value = actionId;
                        param[3].name = "@typeact";
                        param[3].value = typeActId;
                        param[4].name = "@modelplanid";
                        param[4].value = (comboBox1.SelectedItem as NewItem).ParamID;

                        dataGridView1.DataSource = null;
                        dataGridView1.DataSource = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_PlanTmp", 300, param).DefaultView;

                    }
                    f7.Close();
                    f7 = null;
                    
                }
                else MessageBox.Show("Сначала выберите товары или торговые технологии");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button11_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView1.RowCount > 0 && actionId > 0)
                {
                    SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[8];
                    param[0].name = "@actionid";
                    param[0].value = actionId;
                    param[1].name = "@datestart";
                    param[2].name = "@dateend";
                    param[3].name = "@drugcode";
                    param[4].name = "@tradetech";
                    param[5].name = "@pharmcode";
                    param[6].name = "@countpack";
                    param[7].name = "@modeplanid";
                    if (comboBox1.SelectedIndex > -1) param[7].value = (comboBox1.SelectedItem as NewItem).ParamID;
                    else param[7].value = 4;


                    SqlStorProc.sp_params[] param1 = new SqlStorProc.sp_params[6];
                    param1[0].name = "@actionid";
                    param1[0].value = actionId;
                    param1[1].name = "@datestart";
                    param1[1].value = dateStart_prev;
                    param1[2].name = "@dateend";
                    param1[2].value = dateEnd_prev;
                    param1[3].name = "@code";
                    param1[4].name = "@pharmcode";
                    param1[5].name = "@countpack";


                    foreach (DataGridViewRow row in dataGridView1.Rows)
                    {
                        if (dataGridView1.Columns.Contains("Код товара"))
                        {
                            param[3].value = row.Cells["Код товара"].Value;
                            param1[3].value = row.Cells["Код товара"].Value;
                        }
                        if (dataGridView1.Columns.Contains("Код ТТ"))
                        {
                            param[4].value = row.Cells["Код ТТ"].Value;
                            param1[3].value = row.Cells["Код ТТ"].Value;
                        }
                        if (dataGridView1.Columns.Contains("Код группы"))
                        {
                            param[3].value = null;
                            param[4].value = null;
                            param1[3].value = null;
                        }
                        param[5].value = row.Cells["Код аптеки"].Value;
                        param1[4].value = row.Cells["Код аптеки"].Value;
                        param1[5].value = row.Cells["Количество упаковок"].Value;
                        new SqlStorProc().SqlProcExecNonQuery(conn, "pas_Actions_ActionPrevSalesIns", 30, param1);

                        foreach (DataGridViewColumn col in dataGridView1.Columns)
                        {
                            if (col.Name.IndexOf("*") > -1)
                            {
                                param[1].value = StringToDate(col.Name.Replace("*", ""), true).ToString("yyyyMMdd");
                                param[2].value = StringToDate(col.Name.Replace("*", ""), false).ToString("yyyyMMdd");
                                param[6].value = row.Cells[col.Name].Value;

                                if (row.Cells[col.Name].Value != DBNull.Value) new SqlStorProc().SqlProcExecNonQuery(conn, "pas_Actions_ActionPlanIns", 30, param);
                            }
                        }
                    }

                    MessageBox.Show("Все данные сохранены");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private DateTime StringToDate(string date,bool st)
        {
            string month = date.Substring(0, date.IndexOf(" "));
            int year = Convert.ToInt32(date.Substring(date.IndexOf(" ") + 1));
            int month_int = 0;
            switch (month.ToLower())
            {
                case "январь":
                    month_int = 1;
                    break;
                case "февраль":
                    month_int = 2;
                    break;
                case "март":
                    month_int = 3;
                    break;
                case "апрель":
                    month_int = 4;
                    break;
                case "май":
                    month_int = 5;
                    break;
                case "июнь":
                    month_int = 6;
                    break;
                case "июль":
                    month_int = 7;
                    break;
                case "август":
                    month_int = 8;
                    break;
                case "сентябрь":
                    month_int = 9;
                    break;
                case "октябрь":
                    month_int = 10;
                    break;
                case "ноябрь":
                    month_int = 11;
                    break;
                case "декабрь":
                    month_int = 12;
                    break;
            }
            if (st) return new DateTime(year, month_int, 1);
            else return new DateTime(year, month_int, new DateTime(year, month_int, 1).AddMonths(1).AddDays(-1).Day);
        }

        private void ModelPlan2()
        {
            try
            {
                if (dataGridView1.RowCount > 0)
                {
                    DataTable source = (dataGridView1.DataSource as DataView).ToTable();
                    string name = "";
                    string code = "";
                    if (source.Columns.Contains("Товар"))
                    {
                        name = "Товар";
                        code = "Код товара";
                    }
                    else if (source.Columns.Contains("ТТ"))
                    {
                        name = "ТТ";
                        code = "Код ТТ";
                    }
                    else if (source.Columns.Contains("Код группы"))
                    {
                        name = "Группа товаров";
                        code = "Код группы";
                    }
                    DataView view = new DataView(source);
                    view.RowFilter = "[" + name + "] <> ' Итого'";
                    DataTable drugsForPlan;
                    //if (source.Columns.Contains("Процент от продаж")) drugsForPlan = view.ToTable(true, code, name, "Процент от продаж");
                    //else
                    //{
                    drugsForPlan = view.ToTable(true, code, name);
                    drugsForPlan.Columns.Add("Процент от кол-ва чеков");
                    //}
                    foreach (DataRow row in drugsForPlan.Rows)
                        if (row[2].ToString() == "") row[2] = 0;

                    Form7 f7 = new Form7();
                    f7.formText = "Процент от общего количества чеков в аптеке";
                    f7.gbText = "Процент от количества чеков";
                    f7.dt = drugsForPlan;
                    f7.tbText = "0";
                    if (f7.ShowDialog() == DialogResult.OK)
                    {
                        drugsForPlan = f7.dt;
                        drugsForPlan.Columns.RemoveAt(1);

                        source.Columns.RemoveAt(3);
                        source.Columns.RemoveAt(1);
                        while (source.Columns.Count > 5) source.Columns.RemoveAt(5);
                        SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[5];
                        param[0].name = "@perc";
                        param[0].value = drugsForPlan;
                        param[1].name = "@sales";
                        param[1].value = source;
                        param[2].name = "@actionid";
                        param[2].value = actionId;
                        param[3].name = "@typeact";
                        param[3].value = typeActId;
                        param[4].name = "@modelplanid";
                        param[4].value = (comboBox1.SelectedItem as NewItem).ParamID;

                        dataGridView1.DataSource = null;
                        dataGridView1.DataSource = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_PlanTmp", 300, param).DefaultView;

                    }
                    f7.Close();
                    f7 = null;
                    
                }
                else MessageBox.Show("Сначала выберите товары или торговые технологии");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void ModelPlan3()
        {
            try
            {
                if (dataGridView1.RowCount > 0)
                {
                    DataTable source = (dataGridView1.DataSource as DataView).ToTable();
                    string name = "";
                    string code = "";
                    if (source.Columns.Contains("Товар"))
                    {
                        name = "Товар";
                        code = "Код товара";
                    }
                    else if (source.Columns.Contains("ТТ"))
                    {
                        name = "ТТ";
                        code = "Код ТТ";
                    }
                    else if (source.Columns.Contains("Код группы"))
                    {
                        name = "Группа товаров";
                        code = "Код группы";
                    }
                    DataView view = new DataView(source);
                    view.RowFilter = "[" + name + "] <> ' Итого'";
                    DataTable drugsForPlan;
                    //if (source.Columns.Contains("Процент от продаж")) drugsForPlan = view.ToTable(true, code, name, "Процент от продаж");
                    //else
                    //{
                    drugsForPlan = view.ToTable(true, code, name);
                    drugsForPlan.Columns.Add("Количество упаковок");
                    //}
                    foreach (DataRow row in drugsForPlan.Rows)
                        if (row[2].ToString() == "") row[2] = 1;

                    Form7 f7 = new Form7();
                    f7.formText = "План на всю сеть";
                    f7.gbText = "Количество упаковок";
                    f7.dt = drugsForPlan;
                    f7.tbText = "0";
                    if (f7.ShowDialog() == DialogResult.OK)
                    {
                        drugsForPlan = f7.dt;

                        drugsForPlan.Columns.RemoveAt(1);

                        source.Columns.RemoveAt(3);
                        source.Columns.RemoveAt(1);
                        while (source.Columns.Count > 5) source.Columns.RemoveAt(5);
                        SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[5];
                        param[0].name = "@perc";
                        param[0].value = drugsForPlan;
                        param[1].name = "@sales";
                        param[1].value = source;
                        param[2].name = "@actionid";
                        param[2].value = actionId;
                        param[3].name = "@typeact";
                        param[3].value = typeActId;
                        param[4].name = "@modelplanid";
                        param[4].value = (comboBox1.SelectedItem as NewItem).ParamID;

                        dataGridView1.DataSource = null;
                        dataGridView1.DataSource = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_PlanTmp", 300, param).DefaultView;
                    }
                    f7.Close();
                    f7 = null;
                    

                }
                else MessageBox.Show("Сначала выберите товары или торговые технологии");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void ModelPlan4()
        {
            try
            {
                if (actionId > 0)
                {
                    if (dataGridView2.RowCount > 0)
                    {
                        DataTable drugsForPlan;
                        bool cat = false;
                        if (dataGridView2.RowCount > 1 || (dataGridView2.RowCount == 1 && dataGridView2[0, 0].Value.ToString() != "0"))
                        {
                            drugsForPlan = new DataTable();
                            drugsForPlan.Columns.Add("Код аптеки");
                            drugsForPlan.Columns.Add("Аптека");
                            foreach (DataGridViewRow row in dataGridView2.Rows)
                            {
                                object[] ob = new object[2] { row.Cells[0].Value, row.Cells[1].Value };
                                drugsForPlan.Rows.Add(ob);
                            }
                        }
                        else
                        {
                            drugsForPlan = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_CategorySel", 30, new SqlStorProc.sp_params[0]);
                            cat = true;
                        }
                        drugsForPlan.Columns.Add("Количество упаковок");
                        foreach (DataRow row in drugsForPlan.Rows)
                            if (row[2].ToString() == "") row[2] = 1;

                        Form7 f7 = new Form7();
                        if (cat) f7.formText = "План по категориям аптек";
                        else f7.formText = "План по аптекам";
                        f7.gbText = "Количество упаковок";
                        f7.dt = drugsForPlan;
                        f7.tbText = "1";
                        if (f7.ShowDialog() == DialogResult.OK)
                        {
                            drugsForPlan = f7.dt;

                            drugsForPlan.Columns.RemoveAt(1);

                            SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[5];
                            param[0].name = "@perc";
                            param[0].value = drugsForPlan;
                            param[1].name = "@sales";

                            param[2].name = "@actionid";
                            param[2].value = actionId;
                            param[3].name = "@typeact";
                            param[3].value = typeActId;
                            param[4].name = "@modelplanid";
                            param[4].value = (comboBox1.SelectedItem as NewItem).ParamID;

                            DataTable source = new DataTable();
                            source.Columns.Add("pharmcode");
                            source.Columns.Add("drugcode");
                            source.Columns.Add("countpack");
                            source.Columns.Add("totalsum");
                            source.Columns.Add("totalcheck");
                            if (groupBox7.Enabled)
                            {
                                foreach (DataRow row in dt_drugs.Rows)
                                {
                                    object[] r = new object[5] { null, row[1], null, null, null };
                                    source.Rows.Add(r);
                                }
                            }
                            if (groupBox8.Enabled)
                            {
                                if (dataGridView3.RowCount > 1 || Convert.ToInt32(dataGridView3.Rows[0].Cells["Код"].Value) != 0)
                                    foreach (DataGridViewRow row in dataGridView3.Rows)
                                    {
                                        object[] r = new object[5] { null, row.Cells["Код"].Value, null, null, null };
                                        source.Rows.Add(r);
                                    }
                            }
                            param[1].value = source;

                            dataGridView1.DataSource = null;
                            dataGridView1.DataSource = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_PlanTmp", 300, param).DefaultView;

                        }
                        f7.Close();
                        f7 = null;
                    }
                }
                else MessageBox.Show("Сначала выберите акцию");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void ModelPlan5()
        {
            try
            {
                if (dataGridView1.RowCount > 0)
                {
                    Form8 f8 = new Form8();
                    f8.conn = conn;
                    f8.actionId = actionId;
                    if (f8.ShowDialog() == DialogResult.OK)
                    {
                        SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[5];
                        param[0].name = "@perc";
                        param[1].name = "@sales";
                        param[1].value = f8.result_tbl;
                        param[2].name = "@actionid";
                        param[2].value = actionId;
                        param[3].name = "@typeact";
                        param[3].value = typeActId;
                        param[4].name = "@modelplanid";
                        param[4].value = (comboBox1.SelectedItem as NewItem).ParamID;

                        dataGridView1.DataSource = null;
                        dataGridView1.DataSource = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_PlanTmp", 300, param).DefaultView;

                    }
                    f8.Close();
                    f8 = null;

                }
                else MessageBox.Show("Сначала выберите товары или торговые технологии");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button12_Click(object sender, EventArgs e)
        {
            if (actionId > 0)
            {
                if (typeActId == 1 || typeActId == 3)
                {
                    Form9 f9 = new Form9();
                    f9.conn = conn;
                    f9.actionId = actionId;
                    f9.typeAction = typeActId;
                    if(dt_drugs != null)
                        if (dt_drugs.Rows.Count > 0)
                        {
                            DataView view = new DataView(dt_drugs);
                            if (typeActId == 1) f9.drugs = view.ToTable(true, "Код", "Товар");

                        }
                    f9.ShowDialog();
                    f9.Close();
                    f9 = null;
                }
                else MessageBox.Show("Не верный тип акции. Тип акции должен быть 'Товары' или 'Группа товаров'");
            }
            else MessageBox.Show("Не выбрана акция");
        }

        private void ModelPlan6()
        {
            try
            {
                if (dataGridView1.RowCount > 0)
                {
                    DataTable source = (dataGridView1.DataSource as DataView).ToTable();
                    SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[1];
                    par[0].name = "@actionid";
                    par[0].value = actionId;
                    DataTable drugsForPlan = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_ParentDrugsSel", 300, par);

                    if (typeActId != 3)
                    {
                        drugsForPlan.Columns["Процент от продаж"].SetOrdinal(2);
                        drugsForPlan.Columns["Не акция - Код товара"].SetOrdinal(4);
                    }
                    else
                    {
                        drugsForPlan.Columns["Не акция - Код товара"].SetOrdinal(0);
                        drugsForPlan.Columns["Не акция - Товар"].SetOrdinal(1);
                        drugsForPlan.Columns["Процент от продаж"].SetOrdinal(2);
                        drugsForPlan.Columns["Процент от продаж"].ReadOnly = true;
                    }
                    Form7 f7 = new Form7();
                    f7.formText = "Процент от количества проданных упаковок неакционного товара";
                    f7.gbText = "Процент от продаж упаковок";
                    f7.active = false;
                    f7.dt = drugsForPlan;
                    f7.tbText = "100";
                    if (f7.ShowDialog() == DialogResult.OK)
                    {
                        drugsForPlan = f7.dt;                                      
                        drugsForPlan.Columns.RemoveAt(4);
                        drugsForPlan.Columns.RemoveAt(3);
                        drugsForPlan.Columns.RemoveAt(1);

                        source.Columns.RemoveAt(3);
                        source.Columns.RemoveAt(1);
                        while (source.Columns.Count > 5) source.Columns.RemoveAt(5);
                        SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[5];
                        param[0].name = "@perc";
                        param[0].value = drugsForPlan;
                        param[1].name = "@sales";
                        param[1].value = source;
                        param[2].name = "@actionid";
                        param[2].value = actionId;
                        param[3].name = "@typeact";
                        param[3].value = typeActId;
                        param[4].name = "@modelplanid";
                        param[4].value = (comboBox1.SelectedItem as NewItem).ParamID;

                        dataGridView1.DataSource = null;
                        dataGridView1.DataSource = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_PlanTmp", 300, param).DefaultView;

                    }
                    f7.Close();
                    f7 = null;

                }
                else MessageBox.Show("Сначала выберите товары или торговые технологии");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button13_Click(object sender, EventArgs e)
        {
            if (actionId > 0)
                if (MessageBox.Show("Все несохраненные данные, будут удалены.\nВсе равно обновить?", "Внимание!", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[2];
                    param[0].name = "@actionid";
                    param[0].value = actionId;
                    param[1].name = "@pharmcode";
                    DataTable pharmTable = new DataTable();
                    pharmTable.Columns.Add("item");
                    if (dataGridView2.RowCount > 1 || dataGridView2[0, 0].Value.ToString() != "0")
                        foreach (DataGridViewRow row in dataGridView2.Rows)
                            pharmTable.Rows.Add(row.Cells[0].Value);
                    param[1].value = pharmTable;
                    dataGridView1.DataSource = null;
                    dataGridView1.DataSource = new SqlStorProc().SqlProcExecWithQueryOneTable(conn, "pas_Actions_ActionsDetailsSel", 300, param).DefaultView;
                    groupBox13.Enabled = true;
                    foreach (DataGridViewColumn col in dataGridView1.Columns)
                    {
                        if (col.HeaderText.IndexOf("*") == col.HeaderText.Length - 1) col.ReadOnly = false;
                        else col.ReadOnly = true;
                    }
                    dateStart_prev = DateTime.Now.Date;
                    dateEnd_prev = DateTime.Now.Date;
                }
        }

        private void button14_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count > 0)
            {
                if (MessageBox.Show("Удалить выделенные строки?\nВсего выделено строк:" + dataGridView1.SelectedRows.Count, "Внимание", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    foreach (DataGridViewRow row in dataGridView1.SelectedRows)
                    {
                        SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[4];
                        par[0].name = "@actionid";
                        par[0].value = actionId;
                        par[1].name = "@drugcode";
                        if (dataGridView1.Columns.Contains("Код товара")) par[1].value = row.Cells["Код товара"].Value;
                        par[2].name = "@tradetech";
                        if (dataGridView1.Columns.Contains("Код ТТ")) par[2].value = row.Cells["Код ТТ"].Value;
                        par[3].name = "@pharmcode";
                        par[3].value = row.Cells["Код аптеки"].Value;

                        new SqlStorProc().SqlProcExecNonQuery(conn, "pas_Actions_ActionPlanDel", 30, par);
                        dataGridView1.Rows.RemoveAt(row.Index);

                    }
                }
            }
            else MessageBox.Show("Не выбрано ни одной строки");
        }

        private void button15_Click(object sender, EventArgs e)
        {
            try
            {
                new ExpToExcel().ExportToExcel((dataGridView1.DataSource as DataView).ToTable());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button16_Click(object sender, EventArgs e)
        {
            if (dt_drugs != null)
            {
                if (dt_drugs.Rows.Count > 0)
                {
                    SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[2];
                    par[0].name = "@actionid";
                    par[0].value = actionId;
                    par[1].name = "@drugcode";

                    foreach (DataRow row in dt_drugs.Rows)
                    {
                        par[1].value = row[1];
                        new SqlStorProc().SqlProcExecNonQuery(conn, "pas_Actions_DrugGroupIns", 30, par);
                    }
                    MessageBox.Show("Группа сохранена");
                }
                else
                {
                    MessageBox.Show("Не выбраны товары");
                }
            }
        }

        private void button17_Click(object sender, EventArgs e)
        {
            Form10 f10 = new Form10();
            f10.conn = conn;
            f10.actionId = actionId;
            f10.ShowDialog();
            f10.Close();
            f10 = null;
        }

        private void button18_Click(object sender, EventArgs e)
        {
            Form12 f12 = new Form12();
            f12.conn = conn;
            f12.actionId = actionId;
            f12.typePrizeId = typePrizeId;
            f12.ShowDialog();
            f12.Close();
            f12 = null;
        }
    }
}
